﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using AcadPos.Entity;
using TrustX.Dados;
using System.Data.SqlClient;

namespace AcadPos.Data
{
    public class DBMatricula
    {
        #region Lista Matriculas Aluno
        public DataTable listaMatriculasAluno(Matricula matricula)
        {
            string SQL = @"SELECT tc2.ID_CURSO,
                                   tc2.DESCRICAO  AS NOME_CURSO,
                                   tc.CURRICULO,
                                   tm2.DESCRICAO            AS NOME_MODULO,
                                   tt.COD_TURMA,
                                   te.ESTADO,
                                   tp.DESCRICAO                NOME_POLO,
                                   CONVERT(VARCHAR, tm.DATA_CADASTRO, 103) AS DATA_MATRICULA,
                                   --tm.SITUACAO,
                                   tm.ID_TURMA,tm.ID_MATRICULA,
                                   tc2.ID_AREA_CONHECIMENTO,
                                   tp.ID_ESTADO,tm.MATRICULA,
                                   tp.ID_POLO,
                                   CASE tm.SITUACAO
                                        WHEN 'RG' THEN 'Regular'
                                        WHEN 'CA' THEN 'Cancelada'
                                        WHEN 'TR' THEN 'Trancada'
                                        WHEN 'CD' THEN 'Concluída'
                                   END                      AS SITUACAO, 
                                   tt.DATA_FIM AS DATA_CONCLUSAO_CURSO
                            FROM    TB_MATRICULA tm
                            INNER JOIN TB_TURMAS tt
                                ON  tt.ID_TURMA = tm.ID_TURMA
                            INNER JOIN TB_CURRICULO tc
                                ON  tc.ID_CURRICULO = tt.ID_CURRICULO
                            INNER JOIN TB_MODULO tm2
                                ON  tm2.ID_MODULO = tt.ID_MODULO
                            INNER JOIN TB_MODULO_CURRICULO tmc
                                ON  tmc.ID_CURRICULO = tc.ID_CURRICULO
                                AND TMC.ID_MODULO = tm2.ID_MODULO
                            INNER JOIN TB_CURSO tc2
                                ON  tc2.ID_CURSO = tc.ID_CURSO
                            INNER JOIN TB_POLO tp
                                ON  tp.ID_POLO = tt.ID_POLO
                            INNER JOIN TB_ESTADO te
                                ON  te.ID_ESTADO = tp.ID_ESTADO
                            WHERE  tm.MATRICULA = @MATRICULA_ALUNO";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@MATRICULA_ALUNO", SqlDbType.Int, matricula.MatriculaAluno);
            return db.GetDataTable();
        }
        #endregion

        #region Insere 1ª Financeiro
        public bool inserir1ParcelaFinanceiro(Financeiro financeiro)
        {
            string SQL = @"INSERT INTO TB_FINANCEIRO
                                (                                
                                MATRICULA,
                                ID_TURMA,
                                PARCELA,
                                VALOR,
                                DATA_VENCIMENTO,
                                NOSSO_NUMERO,
                                SITUACAO
                                )
                            VALUES
                                (
                                @MATRICULA,
                                @ID_TURMA,
                                @PARCELA,
                                @VALOR,
                                @DATA_VENCIMENTO,
                                @NOSSO_NUMERO,
                                @SITUACAO
                                )";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@MATRICULA", SqlDbType.Int, financeiro.MatriculaAluno);
            db.AddParam("@ID_TURMA", SqlDbType.Int, financeiro.IdTurma);
            db.AddParam("@PARCELA", SqlDbType.Int, financeiro.Parcela);
            db.AddParam("@VALOR", SqlDbType.Decimal, financeiro.Valor);
            db.AddParam("@DATA_VENCIMENTO", SqlDbType.DateTime, financeiro.DataVencimento);
            db.AddParam("@NOSSO_NUMERO", SqlDbType.Int, financeiro.NossoNumero);
            db.AddParam("@SITUACAO", SqlDbType.VarChar, financeiro.Situacao);
            return db.ExecuteNonQuery();
        }
        #endregion

        #region Valida Pagamento
        public bool validarExistenciaPagamento(Financeiro financeiro)
        {
            string SQL = @"SELECT      tf.ID_FINANCEIRO
                                FROM   TB_FINANCEIRO tf
                                WHERE  tf.MATRICULA = @MATRICULA
                                        AND tf.ID_TURMA = @ID_TURMA
                                        AND tf.SITUACAO = 'PAGO'";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@MATRICULA", SqlDbType.Int, financeiro.MatriculaAluno);
            db.AddParam("@ID_TURMA", SqlDbType.Int, financeiro.IdTurma);
            try
            {
                db.AbrirConexao();
                SqlDataReader dr = db.ExecuteReader();
                if (dr.Read())
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                db.FecharConexao();
            }
        }
        #endregion

        #region Valida Existencia de Notas
        public bool validarExistenciaNotas(Matricula matricula)
        {
            string SQL = @"SELECT tmd.ID_MATRICULA_DISCIPLINA
                        FROM   TB_MATRICULA_DISCIPLINA tmd
                        WHERE  tmd.ID_MATRICULA = @ID_MATRICULA
                                AND ((tmd.NOTA1 IS NOT NULL) OR (tmd.NOTA2 IS NOT NULL))";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_MATRICULA", SqlDbType.Int, matricula.IdMatricula);
            try
            {
                db.AbrirConexao();
                SqlDataReader dr = db.ExecuteReader();
                if (dr.Read())
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                db.FecharConexao();
            }
        }
        #endregion

        #region Excluir
        public bool excluir(Matricula matricula)
        {
            string SQL = @"DELETE 
                            FROM   TB_MATRICULA
                            WHERE  ID_MATRICULA = @ID_MATRICULA";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_MATRICULA", SqlDbType.Int, matricula.IdMatricula);
            return db.ExecuteNonQuery();
        }
        #endregion

        #region Inserir Situação Matrícula
        public bool inserirSituacaoMatricula(Matricula matricula)
        {
            string SQL = @"exec SP_ALTERA_SITUACAO_MATRICULA
                                  @ID_MATRICULA,
	                                @SITUACAO,
	                                @PROTOCOLO,
	                                @MOTIVO ";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_MATRICULA", SqlDbType.Int, matricula.IdMatricula);
            db.AddParam("@SITUACAO", SqlDbType.VarChar, matricula.Situacao);
            db.AddParam("@PROTOCOLO", SqlDbType.VarChar, matricula.ProtocoloSituacao);
            db.AddParam("@MOTIVO", SqlDbType.VarChar, matricula.MotivoSituacao);
            if (db.GetDataTable().Rows[0][0].ToString() == "1")
                return true;
            else
                return false;
        }
        #endregion

        #region Excluir Situação Matrícula
        public bool excluirSituacaoMatricula(Matricula matricula)
        {
            string SQL = @"DELETE 
                                FROM   TB_SITUACAO_MATRICULA
                                WHERE  ID_SITUACAO_MATRICULA = @ID_SITUACAO_MATRICULA";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_SITUACAO_MATRICULA", SqlDbType.Int, matricula.IdSituacaoMatricula);
            return db.ExecuteNonQuery();
        }
        #endregion

        #region Lista Situação Matrícula
        public DataTable listaSituacaoMatricula(Matricula matricula)
        {
            string SQL = @"SELECT tsm.DATA_CADASTRO      AS DATA_ACAO,
                                   ID_SITUACAO_MATRICULA,
                                   CASE tsm.SITUACAO
                                        WHEN 'CA' THEN 'Cancelada'
                                        WHEN 'TR' THEN 'Trancada'
                                        ELSE tsm.SITUACAO 
                                   END                    AS SITUACAO,
                                   tsm.PROTOCOLO,
                                   tsm.MOTIVO
                            FROM   TB_SITUACAO_MATRICULA     tsm
                            WHERE  tsm.ID_MATRICULA = @ID_MATRICULA";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_MATRICULA", SqlDbType.Int, matricula.IdMatricula);
            return db.GetDataTable();
        }
        #endregion

        #region Lista Financeiro Matrícula
        public DataTable listaFinanceiroMatricula(Matricula matricula)
        {
            string SQL = @"SELECT  ta.MATRICULA,
                                   ta.NOME AS NOME_CLIENTE,
                                   ta.CPF,
                                   ta.ENDERECO,
                                   ta.CEP,
                                   ta.COMPLEMENTO + ' - ' + CONVERT(VARCHAR,ta.NUMERO) AS COMPLEMENTO,
                                   tf.PARCELA,
                                   tf.ID_FINANCEIRO,
                                   tf.NOSSO_NUMERO,
                                   YEAR(tf.DATA_VENCIMENTO) AS ANO,
                                   CONVERT(VARCHAR, tf.DATA_VENCIMENTO, 103) AS DATA_VENCIMENTO,
                                   CONVERT(VARCHAR, tf.DATA_PAGAMENTO, 103) AS DATA_PAGAMENTO,
                                   tf.VALOR,
                                   CONVERT(
                                       NUMERIC(15, 2),
                                       CASE 
                                            WHEN (
                                                     DATEDIFF(DAY, tf.DATA_VENCIMENTO, GETDATE()) > 0
                                                     AND tf.SITUACAO <> 'PAGO'
                                                 ) THEN tf.VALOR
                                                 + (
                                                     (tf.VALOR * 0.0033) * DATEDIFF(DAY, tf.DATA_VENCIMENTO, GETDATE())
                                                 )
                                            ELSE tf.VALOR
                                       END
                                   ) AS VALOR_AJUSTADO,
                                   CASE tf.SITUACAO
                                        WHEN 'CA' THEN 'Cancelada'
                                        WHEN 'TR' THEN 'Trancada'
                                        ELSE tf.SITUACAO
                                   END AS SITUACAO,
                                   (
                                       SELECT SUM(tf.VALOR)
                                       FROM   TB_FINANCEIRO tf
                                       WHERE  tf.MATRICULA = @MATRICULA
                                              AND tf.ID_TURMA = @ID_TURMA
                                              AND tf.SITUACAO <> 'PAGO'
                                   ) AS VALOR_EM_ABERTA,
                                   (
                                       SELECT SUM(tf.VALOR)
                                       FROM   TB_FINANCEIRO tf
                                       WHERE  tf.MATRICULA = @MATRICULA
                                              AND tf.ID_TURMA = @ID_TURMA
                                              AND tf.SITUACAO = 'PAGO'
                                   ) AS VALOR_PAGO
                            FROM   TB_FINANCEIRO tf
                                   JOIN TB_ALUNO ta
                                        ON  ta.MATRICULA = tf.MATRICULA
                            WHERE  tf.MATRICULA = @MATRICULA
                                   AND tf.ID_TURMA = @ID_TURMA";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_TURMA", SqlDbType.Int, matricula.IdTurma);
            db.AddParam("@MATRICULA", SqlDbType.Int, matricula.MatriculaAluno);
            return db.GetDataTable();
        }
        #endregion

        #region Get Financeiro Dependência
        public DataTable getFichaFinanceiraPendencia(string pNossoNumero)
        {
            string SQL = @"SELECT ta.MATRICULA,
                                   ta.NOME AS NOME_CLIENTE,
                                   ta.CPF,
                                   ta.ENDERECO,
                                   ta.CEP,
                                   ta.COMPLEMENTO + ' - ' + CONVERT(VARCHAR,ta.NUMERO) AS COMPLEMENTO,
                                   tfp.PARCELA,
                                   tfp.ID_FINANCEIRO_DISCIPLINA,
                                   tfp.NOSSO_NUMERO,
                                   YEAR(tfp.DATA_VENCIMENTO)  AS ANO,
                                   CONVERT(VARCHAR, tfp.DATA_VENCIMENTO, 103) AS DATA_VENCIMENTO,
                                   CONVERT(VARCHAR, tfp.DATA_PAGAMENTO, 103) AS DATA_PAGAMENTO,
                                   tfp.VALOR,
                                   CONVERT(NUMERIC(15, 2),CASE WHEN (DATEDIFF(DAY, tfp.DATA_VENCIMENTO, GETDATE()) > 0 AND tfp.SITUACAO <> 'PAGO') THEN tfp.VALOR
                                                 + ((tfp.VALOR * 0.0033) * DATEDIFF(DAY, tfp.DATA_VENCIMENTO, GETDATE()))
                                            ELSE tfp.VALOR END )AS VALOR_AJUSTADO,
                                   CASE tfp.SITUACAO
                                        WHEN 'CA' THEN 'Cancelada'
                                        WHEN 'TR' THEN 'Trancada'
                                        ELSE tfp.SITUACAO
                                   END                       AS SITUACAO
                            FROM   TB_MATRICULA_DISCIPLINA tmd
                                   INNER JOIN TB_FINANCEIRO_PENDENCIA tfp
                                        ON  tfp.ID_FINANCEIRO_DISCIPLINA = tmd.ID_FINANCEIRO_DISCIPLINA
                                   JOIN TB_ALUNO ta
                                        ON  ta.MATRICULA = tmd.MATRICULA
                            WHERE  tfp.NOSSO_NUMERO = @NOSSO_NUMERO
                            AND tfp.SITUACAO = 'EM ABERTA'";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@NOSSO_NUMERO", SqlDbType.VarChar, pNossoNumero);
            return db.GetDataTable();
        }
        #endregion

        #region Get Ficha Financeira
        public DataTable getFichaFinanceira(string pNossoNumero)
        {
            string SQL = @"SELECT ta.MATRICULA,
                               ta.NOME AS NOME_CLIENTE,
                               ta.CPF,
                               ta.ENDERECO,
                               ta.CEP,
                               ta.COMPLEMENTO + ' - ' + CONVERT(VARCHAR,ta.NUMERO) AS COMPLEMENTO
                               tf.PARCELA,
                               tf.ID_FINANCEIRO,
                               tf.NOSSO_NUMERO,
                               YEAR(tf.DATA_VENCIMENTO) AS ANO,
                               CONVERT(VARCHAR, tf.DATA_VENCIMENTO, 103) AS DATA_VENCIMENTO,
                               CONVERT(VARCHAR, tf.DATA_PAGAMENTO, 103) AS DATA_PAGAMENTO,
                               tf.VALOR,
                               CONVERT(
                                   NUMERIC(15, 2),
                                   CASE 
                                        WHEN (
                                                 DATEDIFF(DAY, tf.DATA_VENCIMENTO, GETDATE()) > 0
                                                 AND tf.SITUACAO <> 'PAGO'
                                             ) THEN tf.VALOR
                                             + (
                                                 (tf.VALOR * 0.0033) * DATEDIFF(DAY, tf.DATA_VENCIMENTO, GETDATE())
                                             )
                                        ELSE tf.VALOR
                                   END
                               ) AS VALOR_AJUSTADO,
                               CASE tf.SITUACAO
                                    WHEN 'CA' THEN 'Cancelada'
                                    WHEN 'TR' THEN 'Trancada'
                                    ELSE tf.SITUACAO
                               END AS SITUACAO
                        FROM   TB_FINANCEIRO tf
                               JOIN TB_ALUNO ta
                                    ON  ta.MATRICULA = tf.MATRICULA
                        WHERE tf.NOSSO_NUMERO = @NOSSO_NUMERO
                        AND tf.SITUACAO = 'EM ABERTA'";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@NOSSO_NUMERO", SqlDbType.VarChar, pNossoNumero);
            return db.GetDataTable();
        }
        #endregion

        #region Obtem os dados financeiro da parcela referente a matrícula
        public DataTable obterDadosFinanceiroMatricula(int pNossoNumero)
        {
            string SQL = @"SELECT  ta.MATRICULA,
                                   ta.NOME AS NOME_CLIENTE,
                                   ta.CPF,
                                   ta.ENDERECO,
                                   ta.CEP,
                                   ta.COMPLEMENTO + ' - ' + CONVERT(VARCHAR,ta.NUMERO) AS COMPLEMENTO,
                                   tf.PARCELA,
                                   tf.ID_FINANCEIRO,
                                   tf.NOSSO_NUMERO,
                                   YEAR(tf.DATA_VENCIMENTO) AS ANO,
                                   CONVERT(VARCHAR, tf.DATA_VENCIMENTO, 103) AS DATA_VENCIMENTO,
                                   CONVERT(VARCHAR, tf.DATA_PAGAMENTO, 103) AS DATA_PAGAMENTO,
                                   tf.VALOR,
                                   CONVERT(
                                       NUMERIC(15, 2),
                                       CASE 
                                            WHEN (
                                                     DATEDIFF(DAY, tf.DATA_VENCIMENTO, GETDATE()) > 0
                                                     AND tf.SITUACAO <> 'PAGO'
                                                 ) THEN tf.VALOR
                                                 + (
                                                     (tf.VALOR * 0.0033) * DATEDIFF(DAY, tf.DATA_VENCIMENTO, GETDATE())
                                                 )
                                            ELSE tf.VALOR
                                       END
                                   ) AS VALOR_AJUSTADO,
                                   CASE tf.SITUACAO
                                        WHEN 'CA' THEN 'Cancelada'
                                        WHEN 'TR' THEN 'Trancada'
                                        ELSE tf.SITUACAO
                                   END AS SITUACAO
                            FROM   TB_FINANCEIRO tf
                                   JOIN TB_ALUNO ta
                                        ON  ta.MATRICULA = tf.MATRICULA
                            WHERE  tf.NOSSO_NUMERO = @NOSSO_NUMERO";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@NOSSO_NUMERO", SqlDbType.Int, pNossoNumero);
            return db.GetDataTable();
        }
        #endregion


        #region Gera Parcelas Restantes
        public bool geraParcelasRestantes(Matricula matricula)
        {
            string SQL = @"exec SP_GERA_PARCELAS_RESTANTES
	                            @MATRICULA,
	                            @ID_TURMA,
	                            @PRIMEIRO_VENCIMENTO";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_TURMA", SqlDbType.Int, matricula.IdTurma);
            db.AddParam("@MATRICULA", SqlDbType.Int, matricula.MatriculaAluno);
            db.AddParam("@PRIMEIRO_VENCIMENTO", SqlDbType.DateTime, matricula.PrimeiroVencimento);
            if (db.GetDataTable().Rows[0][0].ToString() == "1")
                return true;
            else
                return false;
        }
        #endregion

        #region Lista Disciplinas Matriculadas Aluno
        public DataTable listaDisciplinasMatriculadasPorAluno(Matricula matricula)
        {
            string SQL = @"SELECT  tmd.ID_MATRICULA_DISCIPLINA,
                                   tmd.ID_MATRICULA,
                                   tmd.ID_DISCIPLINA,
                                   tmc.ID_MATRIZ_CURRICULAR,
                                   tt.ID_TURMA,
                                   td.DESCRICAO       AS NOME_DISCIPLINA,
                                   tmc.CH_DISCIPLINA  AS CH_HORARIA,
                                   tmd.FALTA1,
                                   tmd.FALTA2,
                                   tmd.FALTA3,
                                   tmd.FALTA4,
                                   (((ISNULL(tmd.FALTA1, 0) + ISNULL(tmd.FALTA2, 0) + ISNULL(tmd.FALTA3, 0)+ ISNULL(tmd.FALTA4, 0))
                                       / 
                                    (SELECT 0.25 * (SELECT TMC2.CH_DISCIPLINA
                                                      FROM   TB_MATRIZ_CURRICULAR tmc2
                                                      WHERE  tmc2.ID_MATRIZ_CURRICULAR = tmd.ID_MATRIZ_CURRICULAR))
                                   ))  AS TOTAL_FALTAS,
                                   tmd.NOTA1,
                                   tmd.NOTA2,
                                   tmd.SITUACAO,
                                   tt.COD_TURMA, 
                                   tj.JUSTIFICATIVA
                            FROM   TB_MATRICULA_DISCIPLINA tmd
                                   INNER JOIN TB_DISCIPLINA td
                                        ON  td.ID_DISCIPLINA = tmd.ID_DISCIPLINA
                                   INNER JOIN TB_MATRIZ_CURRICULAR tmc
                                        ON  tmc.ID_DISCIPLINA = td.ID_DISCIPLINA
                                        AND tmc.ID_MATRIZ_CURRICULAR = tmd.ID_MATRIZ_CURRICULAR
                                   INNER JOIN TB_TURMAS tt
                                        ON  tt.ID_TURMA = tmd.ID_TURMA
                                    LEFT JOIN TB_JUSTIFICATIVA tj 
                                                ON tj.ID_MATRICULA_DISCIPLINA = tmd.ID_MATRICULA_DISCIPLINA    
                            WHERE  tmd.ID_MATRICULA = @ID_MATRICULA AND TMD.TIPO_DISCIPLINA = 'RG'";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_MATRICULA", SqlDbType.Int, matricula.IdMatricula);
            return db.GetDataTable();
        }
        #endregion

        #region Lista Disciplinas Matriculadas Aluno Pendentes
        public DataTable listaDisciplinasMatriculadasPorAlunoPendentes(Matricula matricula)
        {
            string SQL = @"SELECT  tmd.ID_MATRICULA_DISCIPLINA,
                                   tmd.ID_MATRICULA,
                                   tmd.ID_DISCIPLINA,
                                   tmc.ID_MATRIZ_CURRICULAR,
                                   tt.ID_TURMA,
                                   td.DESCRICAO       AS NOME_DISCIPLINA,
                                   tmc.CH_DISCIPLINA  AS CH_HORARIA,
                                   tmd.FALTA1,
                                   tmd.FALTA2,
                                   tmd.FALTA3,
                                   tmd.FALTA4,
                                   (((ISNULL(tmd.FALTA1, 0) + ISNULL(tmd.FALTA2, 0) + ISNULL(tmd.FALTA3, 0)+ ISNULL(tmd.FALTA4, 0))
                                       / 
                                    (SELECT 0.25 * (SELECT TMC2.CH_DISCIPLINA
                                                      FROM   TB_MATRIZ_CURRICULAR tmc2
                                                      WHERE  tmc2.ID_MATRIZ_CURRICULAR = tmd.ID_MATRIZ_CURRICULAR))
                                   ))  AS TOTAL_FALTAS,
                                   tmd.NOTA1,
                                   tmd.NOTA2,
                                   tmd.SITUACAO,
                                   tt.COD_TURMA,TFP.NOSSO_NUMERO
                            FROM   TB_MATRICULA_DISCIPLINA tmd
                                   INNER JOIN TB_DISCIPLINA td
                                        ON  td.ID_DISCIPLINA = tmd.ID_DISCIPLINA
                                   INNER JOIN TB_MATRIZ_CURRICULAR tmc
                                        ON  tmc.ID_DISCIPLINA = td.ID_DISCIPLINA
                                        AND tmc.ID_MATRIZ_CURRICULAR = tmd.ID_MATRIZ_CURRICULAR
                                   INNER JOIN TB_TURMAS tt
                                        ON  tt.ID_TURMA = tmd.ID_TURMA
                                   INNER JOIN TB_FINANCEIRO_PENDENCIA tfp
			                            ON  tfp.ID_FINANCEIRO_DISCIPLINA = tmd.ID_FINANCEIRO_DISCIPLINA
                            WHERE  tmd.ID_MATRICULA = @ID_MATRICULA AND TMD.TIPO_DISCIPLINA = 'PD' ";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_MATRICULA", SqlDbType.Int, matricula.IdMatricula);
            return db.GetDataTable();
        }
        #endregion

        #region Insere Justificativa Faltas
        public bool insereJustificativaFalta(Matricula matricula)
        {
            string SQL = @"INSERT INTO TB_JUSTIFICATIVA
                              (
                                ID_MATRICULA_DISCIPLINA,
                                JUSTIFICATIVA
                              )
                            VALUES
                              (
                                @ID_MATRICULA_DISCIPLINA,
                                @JUSTIFICATIVA
                              )";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_MATRICULA_DISCIPLINA", SqlDbType.Int, matricula.IdMatriculaDisciplina);
            db.AddParam("@JUSTIFICATIVA", SqlDbType.VarChar, matricula.JustificativaFalta);
            return db.ExecuteNonQuery();
        }
        #endregion

        #region Lista Disciplinas Matricula Pendente por Equivalentes
        public DataTable listaDiscMatriculaPendente(Disciplina disciplina, int idPolo, int matriculaAluno)
        {
            string SQL = @"exec SP_RETORNA_DISC_PENDENTE_MATRICULA @ID_DISCIPLINA , @ID_POLO , @MATRICULA ";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_DISCIPLINA", SqlDbType.Int, disciplina.IdDisciplina);
            db.AddParam("@ID_POLO", SqlDbType.Int, idPolo);
            db.AddParam("@MATRICULA", SqlDbType.Int, matriculaAluno);
            return db.GetDataTable();
        }
        #endregion

        #region insere financeiro pendencia
        public bool insere1FinanceiroPendencia(Financeiro financeiro)
        {
            string SQL = @"exec SP_INSERE_PARCELA_PENDENCIA
                                    @ID_MATRICULA_DISCIPLINA,
	                                @ID_TURMA,
	                                @ID_MATRIZ_CURRICULAR,
	                                @ID_DISCIPLINA,
	                                @TERMO,
	                                @PARCELA,
	                                @DATA_VENCIMENTO";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_MATRICULA_DISCIPLINA", SqlDbType.Int, financeiro.IdMatriculaDisciplina);
            db.AddParam("@ID_TURMA", SqlDbType.Int, financeiro.IdTurma);
            db.AddParam("@ID_MATRIZ_CURRICULAR", SqlDbType.Int, financeiro.IdMatrizCurricular);
            db.AddParam("@ID_DISCIPLINA", SqlDbType.Int, financeiro.IdDisciplina);
            db.AddParam("@TERMO", SqlDbType.VarChar, financeiro.Termo);
            db.AddParam("@PARCELA", SqlDbType.Int, financeiro.Parcela);
            db.AddParam("@DATA_VENCIMENTO", SqlDbType.DateTime, financeiro.DataVencimento);
            return db.ExecuteNonQuery();
      
        }
        #endregion

        #region Relatório Histórico
        public DataTable relatorioHistorico(Matricula matricula)
        {
            string SQL = @"SELECT  tmd.ID_MATRICULA_DISCIPLINA,
                                   tmd.ID_MATRICULA,
                                   tmd.ID_DISCIPLINA,
                                   tmc.ID_MATRIZ_CURRICULAR,
                                   tt.ID_TURMA,
                                   td.DESCRICAO       AS NOME_DISCIPLINA,
                                   tmc.CH_DISCIPLINA  AS CH_HORARIA,
                                   tmd.FALTA1,
                                   tmd.FALTA2,
                                   tmd.FALTA3,
                                   tmd.FALTA4,
                                   (((ISNULL(tmd.FALTA1, 0) + ISNULL(tmd.FALTA2, 0) + ISNULL(tmd.FALTA3, 0)+ ISNULL(tmd.FALTA4, 0))
                                       / 
                                    (SELECT 0.25 * (SELECT TMC2.CH_DISCIPLINA
                                                      FROM   TB_MATRIZ_CURRICULAR tmc2
                                                      WHERE  tmc2.ID_MATRIZ_CURRICULAR = tmd.ID_MATRIZ_CURRICULAR))
                                   ))  AS TOTAL_FALTAS,
                                   tmd.NOTA1,
                                   tmd.NOTA2,
                                   ((tmd.NOTA1 + tmd.NOTA2) / 2) AS MEDIA,
                                   tmd.SITUACAO,
                                   tt.COD_TURMA,
                                   case tmd.TIPO_DISCIPLINA
                                   WHEN 'RG' THEN 'Regular'
                                   WHEN 'PD' THEN 'Pendência'
                                   END AS TIPO_DISCIPLINA
                            FROM   TB_MATRICULA_DISCIPLINA tmd
                                   INNER JOIN TB_DISCIPLINA td
                                        ON  td.ID_DISCIPLINA = tmd.ID_DISCIPLINA
                                   INNER JOIN TB_MATRIZ_CURRICULAR tmc
                                        ON  tmc.ID_DISCIPLINA = td.ID_DISCIPLINA
                                        AND tmc.ID_MATRIZ_CURRICULAR = tmd.ID_MATRIZ_CURRICULAR
                                   INNER JOIN TB_TURMAS tt
                                        ON  tt.ID_TURMA = tmd.ID_TURMA
                            WHERE  tmd.ID_MATRICULA = @ID_MATRICULA
                            ORDER BY tt.ID_TURMA, td.DESCRICAO";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_MATRICULA", SqlDbType.Int, matricula.IdMatricula);
            return db.GetDataTable();
        }
        #endregion

        #region Atualiza Financeiro
        public bool atualizarFinanceiro(Financeiro financeiro)
        {
            string SQL = @"UPDATE TB_FINANCEIRO
                            SET    DATA_VENCIMENTO     = CASE 
                                                            WHEN ((DATA_VENCIMENTO <> @DATA_VENCIMENTO) AND (@DATA_VENCIMENTO IS NOT NULL)) THEN @DATA_VENCIMENTO
                                                            ELSE DATA_VENCIMENTO
                                                        END,
                                    VALOR               = CASE WHEN ((VALOR <> @VALOR) AND (@VALOR IS NOT NULL)) THEN @VALOR
                                                            ELSE VALOR
                                                        END,
                                    SITUACAO            = CASE 
                                                    WHEN ((SITUACAO <> @SITUACAO) AND (@SITUACAO IS NOT NULL)) THEN @SITUACAO
                                                    ELSE SITUACAO
                                                END,
                                    DATA_PAGAMENTO      = CASE 
                                                            WHEN (SITUACAO <> @SITUACAO AND @SITUACAO = 'PAGO') 
                                                                OR
                                                                (SITUACAO <> @SITUACAO AND @SITUACAO = 'DISPENSADA') THEN 
                                                                GETDATE()
                                                            WHEN (SITUACAO <> @SITUACAO AND @SITUACAO = 'EM ABERTA') THEN 
                                                                NULL
                                                    END
                            WHERE  ID_FINANCEIRO = @ID_FINANCEIRO";

            DataAccess db = new DataAccess(SQL);
            if (financeiro.Valor > 0)
                db.AddParam("@VALOR", SqlDbType.Decimal, financeiro.Valor);
            else
                db.AddParam("@VALOR", SqlDbType.Decimal, DBNull.Value);
            if (financeiro.DataVencimento > DateTime.MinValue)
                db.AddParam("@DATA_VENCIMENTO", SqlDbType.DateTime, financeiro.DataVencimento);
            else
                db.AddParam("@DATA_VENCIMENTO", SqlDbType.DateTime, DBNull.Value);
            db.AddParam("@SITUACAO", SqlDbType.VarChar, financeiro.Situacao);
            db.AddParam("@ID_FINANCEIRO", SqlDbType.Int, financeiro.IdFinanceiro);
            return db.ExecuteNonQuery();
        }
        #endregion

        #region Retorna Limite Qtd Exclusao Parcelas
        public int retornaLimiteExclusaoParcelas(Matricula matricula)
        {
            string SQL = @"exec SP_VALIDA_EXCLUSAO_PARCELAS
                            @MATRICULA,
	                        @ID_TURMA";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_TURMA", SqlDbType.Int, matricula.IdTurma);
            db.AddParam("@MATRICULA", SqlDbType.Int, matricula.MatriculaAluno);
            DataTable dt = db.GetDataTable();
            return Convert.ToInt32(dt.Rows[0][0].ToString());
        }
        #endregion

        #region Exclui parcela financeiro
        public bool excluirParcelaFinanceiro(Financeiro financeiro)
        {
            string SQL = @"DELETE FROM TB_FINANCEIRO WHERE ID_FINANCEIRO = @ID_FINANCEIRO";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_FINANCEIRO", SqlDbType.Int, financeiro.IdFinanceiro);
            return db.ExecuteNonQuery();
        }
        #endregion

        #region Gera Parcelas Restantes Turma
        public bool geraParcelasRestantesTurma(Matricula matricula)
        {
            string SQL = @"
                        
                             exec SP_GERA_PARCELAS_RESTANTES_TURMA	                            
	                            @ID_TURMA,
	                            @PRIMEIRO_VENCIMENTO
                       ";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_TURMA", SqlDbType.Int, matricula.IdTurma);
            db.AddParam("@PRIMEIRO_VENCIMENTO", SqlDbType.DateTime, matricula.PrimeiroVencimento);
            return db.ExecuteNonQuery();
        }
        #endregion

        #region retorna média da instituição
        public decimal retornaMedia()
        {
            string SQL = @"SELECT tp.MEDIA_APROVACAO FROM TB_PARAMETRO tp";

            DataAccess db = new DataAccess(SQL);
            DataTable dt = db.GetDataTable();
            return Convert.ToDecimal(dt.Rows[0][0].ToString());

        }
        #endregion

        #region Get Valor Imposto de Renda
        public double getValorImpostoRenda(int pMatricula, int pAno)
        {
            string SQL = @"DECLARE @VALOR_PARCELAS FLOAT
                            DECLARE @VALOR_PENDENCIAS FLOAT

                            SET @VALOR_PARCELAS = 0
                            SET @VALOR_PENDENCIAS = 0

                            SELECT @VALOR_PARCELAS = SUM(tf.VALOR)
                            FROM   TB_FINANCEIRO tf
                                   JOIN TB_ALUNO ta
                                        ON  ta.MATRICULA = tf.MATRICULA
                            WHERE  ta.MATRICULA = @MATRICULA
                                   AND tf.SITUACAO = 'PAGO'
                                   AND YEAR(tf.DATA_PAGAMENTO) = @ANO
       
                            SELECT @VALOR_PENDENCIAS = ISNULL(tfp.VALOR, 0)
                            FROM   TB_MATRICULA_DISCIPLINA tmd
                                   INNER JOIN TB_FINANCEIRO_PENDENCIA tfp
                                        ON  tfp.ID_FINANCEIRO_DISCIPLINA = tmd.ID_FINANCEIRO_DISCIPLINA
                                   JOIN TB_ALUNO ta
                                        ON  ta.MATRICULA = tmd.MATRICULA
                            WHERE  ta.MATRICULA = @MATRICULA
                                   AND tfp.SITUACAO = 'PAGO'
                                   AND YEAR(tfp.DATA_PAGAMENTO) = @ANO
       
                            SELECT ISNULL(@VALOR_PARCELAS + @VALOR_PENDENCIAS,0) AS VALOR";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@MATRICULA", SqlDbType.Int, pMatricula);
            db.AddParam("@ANO", SqlDbType.Int, pAno);
            return Convert.ToDouble(db.GetDataTable().Rows[0]["VALOR"].ToString());
        }
        #endregion

        #region Relatorio Pre-Matricula
        public DataTable relatorioPreMatricula(Curso curso, DateTime dtInicio, DateTime dtFim)
        {
            string SQL = @"   SELECT   ta.MATRICULA,
                                       ta.NOME,
                                       '(' + RTRIM(ta.DDD_TELEFONE) + ')' + ' ' + ta.TELEFONE AS TELEFONE,
                                       '(' + RTRIM(ta.DDD_CELULAR) + ')' + ' ' + ta.CELULAR AS CELULAR,
                                       ta.EMAIL,
                                       tf.DATA_CADASTRO,
                                       tc2.DESCRICAO AS CURSO,
                                        CONVERT(VARCHAR,ta.MATRICULA) + ' - ' + NOME AS MATRICULA_NOME
                                FROM   TB_FINANCEIRO tf
                                       INNER JOIN TB_ALUNO ta
                                            ON  ta.MATRICULA = tf.MATRICULA
                                       INNER JOIN TB_TURMAS tt
                                            ON  tt.ID_TURMA = tf.ID_TURMA
                                       INNER JOIN TB_CURRICULO tc
                                            ON  tc.ID_CURRICULO = tt.ID_CURRICULO
                                       INNER JOIN TB_CURSO tc2
                                            ON  tc2.ID_CURSO = tc.ID_CURSO
                                WHERE  tf.PARCELA = 1
                                       AND Tf.SITUACAO = 'EM ABERTA'
                                       AND tf.DATA_CADASTRO >= @DATA_INICIO
                                       AND tf.DATA_CADASTRO < DATEADD(DAY, 1, @DATA_FIM)
                                       AND (tc2.ID_AREA_CONHECIMENTO = @ID_AREA_CONHECIMENTO OR 0 = @ID_AREA_CONHECIMENTO)
                                       AND (tc2.ID_CURSO = @ID_CURSO OR @ID_CURSO = 0)";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@DATA_INICIO", SqlDbType.DateTime, dtInicio);
            db.AddParam("@DATA_FIM", SqlDbType.DateTime, dtFim);
            db.AddParam("@ID_AREA_CONHECIMENTO", SqlDbType.Int, curso.IdAreaConhecimento);
            db.AddParam("@ID_CURSO", SqlDbType.Int, curso.IdCurso);
            return db.GetDataTable();
        }
        #endregion#region Get Solicitações Certificado

        #region
        public DataTable getSolicitacoesCertificado(int pMatricula)
        {
            string SQL = @"SELECT tsc.ID_SOLICITACAO,
                                   tsc.ID_MATRICULA,
                                   tsc.VIA,
                                   tsc.DATA_CERT_SOLICITADO,
                                   tsc.DATA_CONCLUSAO_CURSO,
                                   tsc.TITULO_TCC,
                                   tsc.ID_PROFISSIONAL,
                                   tsc.DATA_CERT_IMPRESSO,
                                   tsc.DATA_CERT_PRONTO,
                                   tsc.DATA_CERT_ENTREGUE,
                                   tp.ID_PROFISSIONAL,
                                   tp.NOME AS PROFISSIONAL,
                                   'TESTE' AS CHAVE_VALIDACAO
                            FROM   dbo.TB_SOLICITACAO_CERTIFICADO tsc
                                   JOIN TB_PROFISSIONAL tp
                                        ON  tp.ID_PROFISSIONAL = tsc.ID_PROFISSIONAL
                            WHERE  tsc.ID_MATRICULA = @MATRICULA";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@MATRICULA", SqlDbType.Int, pMatricula);
            return db.GetDataTable();
        }
        #endregion

        #region Solicitar Certificado
        public bool solicitarCertificado(int pMatricula, DateTime dataConclusaoCurso, string tituloTCC, int idProfissional, DateTime dataImpresso, DateTime dataPronto, DateTime dataEntregue)
        {
            string SQL = @"INSERT INTO TB_SOLICITACAO_CERTIFICADO
                              (
                                ID_MATRICULA,
                                VIA,
                                DATA_CERT_SOLICITADO,
                                DATA_CONCLUSAO_CURSO,
                                TITULO_TCC,
                                ID_PROFISSIONAL,
                                DATA_CERT_IMPRESSO,
                                DATA_CERT_PRONTO,
                                DATA_CERT_ENTREGUE
                              )
                            VALUES
                              (
                                @ID_MATRICULA,
                                1,
                                GETDATE(),
                                @DATA_CONCLUSAO_CURSO,
                                @TITULO_TCC,
                                @ID_PROFISSIONAL,
                                @DATA_CERT_IMPRESSO,
                                @DATA_CERT_PRONTO,
                                @DATA_CERT_ENTREGUE
                              )";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_MATRICULA", SqlDbType.Int, pMatricula);
            db.AddParam("@DATA_CONCLUSAO_CURSO", SqlDbType.DateTime, dataConclusaoCurso);
            db.AddParam("@TITULO_TCC", SqlDbType.VarChar, tituloTCC);
            db.AddParam("@ID_PROFISSIONAL", SqlDbType.Int, idProfissional);
            db.AddParam("@DATA_CERT_IMPRESSO", SqlDbType.DateTime, dataImpresso);
            db.AddParam("@DATA_CERT_PRONTO", SqlDbType.DateTime, dataPronto);
            db.AddParam("@DATA_CERT_ENTREGUE", SqlDbType.DateTime, dataEntregue);
            return db.ExecuteNonQuery();
        }
        #endregion

        #region Verifica se aluno é inadimplente
        public bool isInadimplente(int pMatricula)
        {
            string SQL = @"DECLARE @VALOR_PARCELAS FLOAT
                                    DECLARE @VALOR_PENDENCIAS FLOAT

                                    SET @VALOR_PARCELAS = 0
                                    SET @VALOR_PENDENCIAS = 0

                                    SELECT @VALOR_PARCELAS = SUM(tf.VALOR)
                                    FROM   TB_FINANCEIRO tf
                                           JOIN TB_ALUNO ta
                                                ON  ta.MATRICULA = tf.MATRICULA
                                    WHERE  ta.MATRICULA = @MATRICULA
                                           AND tf.SITUACAO = 'EM ABERTA'
       
                                    SELECT @VALOR_PENDENCIAS = ISNULL(tfp.VALOR, 0)
                                    FROM   TB_MATRICULA_DISCIPLINA tmd
                                           INNER JOIN TB_FINANCEIRO_PENDENCIA tfp
                                                ON  tfp.ID_FINANCEIRO_DISCIPLINA = tmd.ID_FINANCEIRO_DISCIPLINA
                                           JOIN TB_ALUNO ta
                                                ON  ta.MATRICULA = tmd.MATRICULA
                                    WHERE  ta.MATRICULA = @MATRICULA
                                           AND tfp.SITUACAO = 'EM ABERTA'
       
                                    SELECT ISNULL(@VALOR_PARCELAS + @VALOR_PENDENCIAS, 0) AS VALOR";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@MATRICULA", SqlDbType.Int, pMatricula);
            return Convert.ToDecimal(db.GetDataTable().Rows[0]["VALOR"]) > 0;
        }
        #endregion    }
    }
}
